<?php

header('Content-Type: text/plain');

require_once('common.php');

$envConfig = Configuration::getInstance();

$log = Log::instance();
$log->open($envConfig->getLogFileName(), $envConfig->getLogLevel());

try {
	echo "Connecting to database...\n\n";
	$db = $envConfig->newPdoConnection();
	
	$pdoConfig = $envConfig->getPdoConfig();
	foreach($pdoConfig->getTableNames() as $tableName) {
		$tableCreateStatement = $pdoConfig->getCreateStatement($tableName);
		
		echo "Dropping table [$tableName] ...\n";
		$db->exec('DROP TABLE IF EXISTS ' . $tableName);
		#$db->exec('DROP TABLE ' . $tableName);
		
		echo "Creating table [$tableName] by statement:\n  $tableCreateStatement\n\n";
		$db->exec($tableCreateStatement);
	}
	
	// prepare categories
	$sqlCountCategories = 'SELECT count(*) AS cnt FROM ' . TBL_USER;
	$stmt = $db->query($sqlCountCategories);
	$rows = $stmt->fetchAll();
	$cnt = $rows[0]['cnt'];
	
	if($cnt == 0) {
		$db->exec("INSERT INTO ".TBL_USER." (username, password, email) VALUES ('test', '". md5('test') ."', '@')"); # 1
		$db->exec("INSERT INTO ".TBL_USER." (username, password, email) VALUES ('Christoph Pickl', '". md5('cp') ."', '@')"); #2
		$db->exec("INSERT INTO ".TBL_USER." (username, password, email) VALUES ('Anna Nym', '". md5('fm') ."', '@')"); #3
		$db->exec("INSERT INTO ".TBL_USER." (username, password, email) VALUES ('Max Mustermann', '". md5('a') ."', '@')"); #4
		$db->exec("INSERT INTO ".TBL_USER." (username, password, email) VALUES ('Otto Normal', '". md5('b') ."', '@')"); #5
		
		insertCategory($db, true, 'General Offered Category'); # 1o
		insertCategory($db, false, 'Java&Co'); # 1n
		insertCategory($db, false, 'Frameworks'); # 2n
		insertCategory($db, false, 'Languages'); # 3n
		insertCategory($db, false, 'Web'); # 4n
		insertCategory($db, false, 'Misc'); # 5n
		
		insertTopic($db, true, 2, 'AspectJ', 1); # 1
		insertTopic($db, true, 3, 'MySQL', 1); # 2
		insertTopic($db, true, 2, 'LeJOS', 1); # 3
		
		insertTopic($db, false, 2, 'Scala', 3); # 4
		insertTopic($db, false, 3, 'Spring', 2); # 5
		insertTopic($db, false, 4, 'Hibernate', 2); # 6
		insertTopic($db, false, 5, 'Struts', 4); # 7
		
		insertVote($db, 3, 1);
		insertVote($db, 2, 3);
		insertVote($db, 3, 3);
		insertVote($db, 4, 3);
		insertVote($db, 5, 3);
		
		insertVote($db, 5, 4);
		insertVote($db, 5, 5);
		insertVote($db, 3, 6);
		insertVote($db, 4, 6);
		insertVote($db, 5, 6);
		
	}
	
	$db = null;
	$log->close();
} catch(PDOException $e) {
	echo $e->getMessage();
	$log->closeSaveIfNecessary();
}

echo "\n\n!FINISHED!";

function insertVote(&$db, $userId, $topoicId) {
	$db->exec("INSERT INTO vote (user_id, topic_id, created) VALUES ($userId, $topoicId, current_timestamp)");
}

function insertCategory(&$db, $isOffered, $title) {
	$db->exec("INSERT INTO " . TBL_CATEGORY . " (title) VALUES ('$title')"); # topic_id 1
	$db->exec("INSERT INTO " . ($isOffered ? TBL_OFFERED_CATEGORY : TBL_NEEDED_CATEGORY) . " (category_id) VALUES(" . $db->lastInsertId() . ")");
}
function insertTopic(&$db, $isOffered, $userId, $title, $proper_category_id) {
	$db->exec("INSERT INTO " . TBL_TOPIC . " (user_id, title, created) VALUES ($userId, '$title', current_timestamp)"); # topic_id 1
	$db->exec("INSERT INTO " . ($isOffered ? TBL_OFFERED_TOPIC : TBL_NEEDED_TOPIC ) . " (topic_id, " . ($isOffered ? 'offered' : 'needed') . "_category_id) VALUES (" . $db->lastInsertId() . ", $proper_category_id)");
}

?>
